No matter what business you are in, it’s a pretty sure thing that someone is going to want to monitor how efficiently and productively you are working. Being able to show these results over time is crucial to maintaining the health of the long-term relationship.
To us, reporting has become much less about the data, and more about the story you can tell with it. Not to say that data isn’t important, but while a big spike in traffic is awesome, the drop that comes after it might not necessarily be a bad thing. It could be seasonal. Or maybe it’s just leveling back after the brand was mentioned in a news report. To be totally honest, the same thing could apply to the spike itself, so hold off on those celebrations!
For the sake of transparency, this is something that we always felt we could improve on more at Kahena. We have tried it all, from email write ups, to using straight ranking reports (blerg, I know – so 2007), to a template-based Excel model (which is what we currently use), and even as far as planning a future web-based reporting platform for all of our clients to log in to and utilize as needed.
This whole project really started in the wake of Google blocking all keyword data and turning it all into [not provided]. This really forced us to break old and bad habits and redefine what important metrics and insights we were sharing with clients.
We had two goals in mind during this project which were to:
- Automate the report as much as possible so that we have more time to analyze the data and provide deeper, more valuable insights, both for our clients and our own internal strategies.
- Keep the template intact so there was standardization across our reports.
Categorizing all the data
We held a brainstorm and decided to compartmentalize the data into 3 different categories:
- KPIs: These are the key performance indicators that we felt show bottom line progress from the campaign.
- Leading Indicators: These metrics add value to the story by exhibiting a correlation with the KPIs. These are all things that clients need to know to understand the KPI outcomes.
- Nice to know: These metrics don’t necessarily make or break the campaign, but are notable and worth mentioning because they provide some insight into overall performance.
Here’s what the brainstorm looked like after our meeting:
What this ultimately turned into is the following (click on the report for a full-sized version):
Now, let me be clear, this could have gone in lots of different directions, and in many cases it does depending on the client. We chose the metrics above for our template because we felt they provided the best high-level insight. Let me explain.
The KPI’s we thought were most important were traffic and goals. That was a relatively simple one to decide since this is ultimately what clients hire us to improve.
Leading indicators were a bit more challenging to solidify. We decided that for the purposes of story-telling, landing page data would be able to explain to our clients the progress we were making around specific landing pages we were optimizing. It also would provide more data into which *groups* of keywords were doing well and which needed a bit more attention. We also put an emphasis on vertical rankings (we partner with Rank Ranger to get that data). We specifically did not want to focus on individual keywords, although there is still a section for specific changes which are chosen to highlight campaign progress.
The nice to know information is the one section that we didn’t trend, and in the future we still might decide to do that. For now, we highlight user engagement data like pages per visit, bounce rate and average time on site. The one area we do trend here is site indexation, because that can have a major effect on a site’s search engine health and provides an early warning to any index bloat issues
A bonus area we added to the report allows our account managers to think creatively regarding non-standard data highlights and observations. Interestingly, this is often the section that gets the most comments from clients.
The most time-consuming piece in this whole process was setting up the initial template. The report itself is actually quite simple after that. The client-facing portion is one tab, which allows us to PDF everything, and it pulls data from other data tabs.
Most of the tabs have a similar table which has the date and the metric you want to visualize. This is just copying and pasting numbers from Analytics – nothing fancy. But, if you’re curious, the tables where we put this data look like this:
Pulling the data
Once set up, the only data we are pulling from Analytics is the number of:
- Total organic visits, broken down by the traffic source
- Goal conversions (transactions and revenue for e-commerce)
- Micro goal conversions (newsletter signups, or event tracking on important buttons)
- Total page views
- Bounce rate
- Time spent on site
The report basically does the rest since the data sources for all the graphs and charts have already been selected.
We also download landing page data from Google Analytics which includes two segments: organic and total traffic compared by current and previous month. Similarly, when added into the appropriate columns, the template populates the data into this section:
All this data pulling and automation helps a lot with the time element. The problem we found was with a 10 person team (let alone a team of significantly more people), everyone wanted some level of customization, which was completely understandable. Each client is going to have specific requests, and each account manager is going to want to tweak it to their liking. The problem with everyone customizing the template is that it often resulted in some elements displaying weirdly when we made it into a PDF, or worse, broke some of the formulas that caused the template to work.
When we investigated this, we found that 80% of our clients could use one of 2 templates:
- A conversion and micro conversion report which reported on a goal we had set up and included events and micro conversions we were targeting
- An e-commerce report which reported on transactions and revenue generated from organic search
Keeping the rest of the report intact involved some creativity. For this, if you are not familiar with offsets, then I suggest paying close attention.
The offset function creates magic
This is a little-used Excel function from what I have found, but it’s super powerful. What it does is:
“Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned.” Source: Microsoft Excel Help
In other words, one cell could control pivoting the entire report between months. Every single table that you have in the report can be linked to one cell and change as that cell changes. Even better, if you can create a button that is connected to this cell, with the simple click of the button, it can adjust data in various parts of the template such as the date, the header, footer, and even provide a way for clients to navigate themselves through the data (if set up correctly).
Here is how it works for us:
In many ways, this serves as a database that holds historical data so it’s saved for the future. Using this, we can show a trended analysis of the progress we have made over time without having to do a ton of data pulling, saving us a significant amount of time.
Given the amount of reports we, as an agency, run on a monthly basis, we need to find better ways of automation without the restrictions that come with third party tools. We have started developing an interactive web-based reporting tool built on the Microsoft Reporting framework. This will work by setting up scheduled jobs to collect data from various data sources (including Google Analytics) and automatically store them in our database so that we don’t even have to pull the data. This will allow our team (and in the future – our clients) to access this data via pre-templated online reports and more time to tell stories and analyze trends.
For now, we are still using Excel, which our clients still love.
The real point that I want to stress is that no matter what method you use for reports, the story is key. The data is crucial, but the insight around the data is what management wants to see. They want to visually see what is happening with their business, and understand through our analysis why it’s happening.
We have received an incredible amount of feedback from clients who have said things like “I really LOVE the format of having the charts or graphs, and then having your commentary/insights right next to it…TOTALLY works for me”
So they are happy. We are happy.
And that’s our process – I hope you found something you can take away for your reports as well.
One final tip to build the report
This is not something that could be done alone. It really took the entire team’s effort, ranging from junior associates up to the CEO, getting buy-in, agreeing on the metrics, and believing in the vision for what we were trying to accomplish with reporting. The ideas and feedback were invaluable and they should all be proud of what we accomplished together.
Download the template
To help you get started, we have made the template available for you to download. I am happy to answer any specific questions in the comments and help you as you set up your own report for you and your clients.
